var queues = require('mysql-queues');
const DEBUG = true;
var select = function(connection, callback) {
	// console.log('11111111111');
	var sql = 'select * from organization';
	connection.query(sql, function(err, rows) {
		if (err) {
			console.log(err);
			console.log(err.stack);
		}
		// console.log("-----model--select:",rows);
		callback(rows);
		connection.release();
	});
},
	add = function(connection, data, callback) {
		queues(connection, DEBUG);
		// console.log('11111111111');
		var sql = 'insert into organization(orgName,orgType,parentId, orgAddress, orgManager, systemId) values(?,?,?,?,?,?)';
		var trans = connection.startTransaction();
		trans.query(sql, [data.orgName, data.orgType, data.parentId, data.orgAddress, data.orgManager, data.systemId], function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					// console.log(info);
				});
			}
			// console.log('rows.insertId:', rows.insertId);
			callback(rows);
		});
		trans.execute();
		console.log('execute add');
		connection.release();
	},
	delet = function(connection, id, callback) {
		queues(connection, DEBUG);
		var sql = 'delete from organization where id=?';
		var trans = connection.startTransaction();
		trans.query(sql, id, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					console.log(info);
				});
			}
			console.log('org--delet--rows', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute');
		connection.release();
	},
	update = function(connection, data, callback) {
		queues(connection, DEBUG);
		// console.log("---model-----data---------");
		// console.log(data);
		// console.log("--------id--------" + data.id);
		var sql = 'update organization set orgName=? ,orgType=? , parentId=? , orgAddress=? , orgManager=? , systemId=? where id=?';
		var trans = connection.startTransaction();
		trans.query(sql, [data.orgName, data.orgType, data.parentId, data.orgAddress, data.orgManager, data.systemId, data.id], function(err, rows) {
			console.log('updatesql:', sql);
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.commit(function(err, info) {
					// console.log('info:',info);
				});
			}
			// console.log('rows--', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute update');
		connection.release();
	},
	findOne = function(connection, name, callback) {
		var sql = 'select * from organization where orgName=?';
		connection.query(sql, name, function(err, rows) {
			if (err) {
				console('err:1111', err);
			}
			connection.release();
			callback(rows);
		});
	},

	//校验机构名称
	checkOrgName = function(connection, orgData, callback) {
		// console.log("-------checkOrgName------");
		// console.log(orgData);
		/*
         如果类型为子公司，则全局查询是否重名，如果是部门类型，则查询
         同一父节点下是否重名
		*/
		if (orgData.orgType == "C") {
			var sql = "select * from organization where orgName=?";
			connection.query(sql, orgData.orgName, function(err, rows) {
				if (err) {
					console.log('checkOrgName_err:', err);
				}
				connection.release();
				callback(rows);
			});
		} else {
			var sql = "select * from organization where parentId=?";
			connection.query(sql, orgData.parentId, function(err, rows) {
				if (err) {
					console.log('checkOrgName_err:', err);
				}
				connection.release();
				var sendData = [];
				rows.forEach(function(org) {
					if (org.orgName == orgData.orgName) {
						sendData.push(org);
					}
				});
				callback(sendData);
			});

		}
	},

	//查询体系下的机构
	selOrgBySys = function(connection, sysid, callback) {
		var sql = "select * from organization where systemId=?";
		connection.query(sql, sysid, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},
	//根据机构id查询机构信息
	selOrgById = function(connection, orgId, callback) {
		var sql = "select o2.*, e.name empName from (select o.*, s.sysName from organization o left join system s on o.systemId=s.id)o2 left join employee e on o2.orgManager = e.id where o2.id = ?";
		connection.query(sql, orgId, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
			}
			callback(rows);
			connection.release();
		});
	},

	//通过id查询机构下的子机构
	selChild = function(connection, orgid, callback) {
		var sql = "select * from organization where parentId=?";
		connection.query(sql, orgid, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},
	//查询机构下的员工
	selEmployee = function(connection, orgid, callback) {
		var sql = "select * from employee where departmentId=?";
		connection.query(sql, orgid, function(err, rows) {
			console.log("model--selEmployee--rows", rows);
			callback(rows);
			connection.release();
		});
	},

	//查询所有机构是部门的信息
	selOrgIsDepart = function(connection, callback) {
		var sql = "select * from organization where orgType='D'";
		connection.query(sql, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},
	//查询当前用户机构区域
	selOrgAddress = function(connection, id, callback) {
		var sql = 'select o.orgAddress from user u,employee e,organization o where u.employeeId=e.id and e.departmentId=o.id and u.id=?';
		connection.query(sql, id, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},
	//查询当前用户负责的机构
	selOrgId = function(connection, id, callback) {
		var sql = 'select o.id,o.orgName from organization o,user u where o.orgManager=u.employeeId and u.id=?';
		connection.query(sql, id, function(err, rows) {
			// console.log("model--selOrgId--rows", rows);
			callback(rows);
			connection.release();
		});
	},

	//查询根节点（父节点id为-1）
	selRoot = function(connection, callback) {
		var sql = "select * from organization where parentId = -1";
		connection.query(sql, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				callback(null);
			} else {
				callback(rows);
			}
			connection.release();
		})
	},
	findOrgByIds = function(connection, ids, callback) {
		var sqlStr = "(";
		var sql = "";
		if (ids.length != 0) {
			for (var i = 0; i < ids.length; i++) {
				if (i < (ids.length - 1)) {
					sqlStr = sqlStr + ids[i] + ",";
				} else {
					sqlStr = sqlStr + ids[i] + ")";
				}
			}
			sql = "select * from organization o where o.parentId in" + sqlStr;
			connection.query(sql, function(err, rows) {
				if (err) {
					console.log(err);
					console.log(err.stack);
					callback(null);
				} else {
					callback(rows);
				}
				connection.release();
			});
		} else {
			callback(null);
		}

	},
	findSysNameByOrgid = function(connection, orgId, callback) {
		var sql = 'select s.sysName from organization o ,system s where o.systemId=s.id and o.id=?';
		connection.query(sql, orgId, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
			}
			
			callback(rows);

			connection.release();
		});
	},
	findById = function(connection, orgId, callback) {
		var sql = 'select o.id, o.orgName, o.parentId, o.systemId,o.orgAddress, o.orgManager , e.name from organization o left join employee e on o.orgManager = e.id where o.id=?';
		connection.query(sql, orgId, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
			}
			callback(rows);

			connection.release();
		});
	};

var sql2 = 'select o.id from organization o where o.orgManager=?';
module.exports = {
	select: select,
	add: add,
	delet: delet,
	update: update,
	findOne: findOne,
	findById: findById,
	checkOrgName: checkOrgName,
	selOrgBySys: selOrgBySys,
	selChild: selChild,
	selEmployee: selEmployee,
	selOrgIsDepart: selOrgIsDepart,
	selOrgById: selOrgById,
	selOrgId: selOrgId,
	selRoot: selRoot,
	selOrgAddress: selOrgAddress,
	findOrgByIds: findOrgByIds,
	findSysNameByOrgid:findSysNameByOrgid
};